﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using OBUCare.Core.Models;
using OBUCare.Core.DataAccess;
using Telerik.Web.Mvc;
using OBUCare.Web.Models;
using OBUCare.Web.Infrastructure;
using OBUCare.Core.BL;
using OBUCare.Core.Common;

namespace OBUCare.Web.Controllers
{


    /// <summary>
    /// 报表
    /// </summary>
    public class ReportController : Controller
    {
        OBUCareOrderMgr obuMgr = new OBUCareOrderMgr();

        private OBU_CAREContext db = new OBU_CAREContext();

        protected override void Dispose(bool disposing)
        {
            base.Dispose(disposing);
            obuMgr.Dispose();
        }

        public ActionResult Index()
        {
            return RedirectToAction("Daily");
        }

        public ActionResult ParentPage()
        {
            return View();
        }

        #region Daily 营业厅维修业务日报表

        public ActionResult Daily()
        {
            return View();
        }


        /// <summary>
        /// 根据sql返回查询后操作员每日发票统计
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [GridAction]
        public ActionResult ListOperatorInvoicesSqlItems(string sql, string searchSql)
        {
            var result = getOperatorInvoices(sql, searchSql);
            var temp = new GridModel(result);
            return View(temp);
        }

        /// <summary>
        /// 根据sql返回查询后操作员每日发票统计数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="searchSql"></param>
        /// <returns></returns>
        private IEnumerable<View_Operator_InvoiceInfos> getOperatorInvoices(string sql, string searchSql)
        {
            //根据编号判断查询权限
            string whereAgentNo = Request["agentNO"] == null || Request["agentNO"].ToString() == "" ? "" : " AND AGENT_NO='" + Request["agentNO"].ToString() + "'";
            string modelID = Request["menuNames"] == null || Request["menuNames"].ToString() == "" ? "" : Request["menuNames"].ToString();
            //如果营业厅的权限为本营业厅 则界面默认显示当前登录员的操作发票统计信息
            // string strOperator = whereAgentNo == "" ? "" : " AND OPERATOR_NO='" + UIUtility.GetCurrentUser().OPERATOR_NO + "' ";

            string tempSql = "";
            if ((!sql.IsNullOrEmpty()) && (!searchSql.IsNullOrEmpty()))
                tempSql = sql + " and " + searchSql;
            else
                tempSql = string.Format("{0} {1}", sql, searchSql);

            string normalSql = tempSql.Trim() == "" ?
              " [RECORD_TIME]>='" + DateTime.Now.ToShortDateString() + "' AND [RECORD_TIME] <'" + DateTime.Now.AddDays(1).ToShortDateString() + "' " :
              "";//如果没有任何条件 则默认显示当天的所有信息

            //如果权限为本营业厅操作员权限 则增加登录员查询条件
            OPERATOR operatorInfo = UIUtility.GetCurrentUser();
            string models = UIUtility.getOperatorByModule(operatorInfo, Convert.ToInt32(modelID));
            string strOperator = "";
            if (models == "6")
                strOperator = " AND OPERATOR_NO='" + UIUtility.GetCurrentUser().OPERATOR_NO + "' ";

            var result = obuMgr.GetOperatorInvoices(tempSql + normalSql + whereAgentNo + strOperator);
            return result;
        }

        /// <summary>
        /// 根据sql返回查询后操作员每日收款退款统计
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [GridAction]
        public ActionResult ListOperatorReceivedReturnedMoneySqlItems(string sql, string searchSql)
        {

            var result = getOperatorReceivedReturnedMoney(sql, searchSql);
            var temp = new GridModel(result);
            return View(temp);
        }

        /// <summary>
        /// 根据sql返回查询后操作员每日收款退款统计数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="searchSql"></param>
        /// <returns></returns>
        private IEnumerable<View_Received_Returned_Money> getOperatorReceivedReturnedMoney(string sql, string searchSql)
        {
            //根据编号判断查询权限
            string whereAgentNo = Request["agentNO"] == null || Request["agentNO"].ToString() == "" ? "" : " AND AGENT_NO='" + Request["agentNO"].ToString() + "'";
            string modelID = Request["menuNames"] == null || Request["menuNames"].ToString() == "" ? "" : Request["menuNames"].ToString();
            ////如果营业厅的权限为本营业厅 则界面默认显示当前登录员的操作发票统计信息
            //string strOperator = whereAgentNo == "" ? "" : " AND OPERATOR_NO='" + UIUtility.GetCurrentUser().OPERATOR_NO + "' ";

            string tempSql = "";
            if ((!sql.IsNullOrEmpty()) && (!searchSql.IsNullOrEmpty()))
                tempSql = sql + " and " + searchSql;
            else
                tempSql = string.Format("{0} {1}", sql, searchSql);
            string normalSql = tempSql.Trim() == "" ?
            " [RECORD_TIME]>='" + DateTime.Now.ToShortDateString() + "' AND [RECORD_TIME] <'" + DateTime.Now.AddDays(1).ToShortDateString() + "' " :
            "";//如果没有任何条件 则默认显示当天的所有信息

            //如果权限为本营业厅操作员权限 则增加登录员查询条件
            OPERATOR operatorInfo = UIUtility.GetCurrentUser();
            string models = UIUtility.getOperatorByModule(operatorInfo, Convert.ToInt32(modelID));
            string strOperator = "";
            if (models == "6")
                strOperator = " AND OPERATOR_NO='" + UIUtility.GetCurrentUser().OPERATOR_NO + "' ";

            var result = obuMgr.GetOperatorReceivedReturnedMoney(tempSql + normalSql + whereAgentNo + strOperator);
            return result;
        }

        [HttpPost]
        public ActionResult DailySearch(DateTime? FromCreatedDate, DateTime? ToCreatedDate, string Operator, string Agent,
            string txtOperator) //也可以用这种格式接收参数public ActionResult Search(FormCollection form)
        {
            try
            {
                var sql = obuMgr.GetSearhDailyReportSql(FromCreatedDate, ToCreatedDate,
                    Operator.ChangAllTextToNull(), Agent.ChangAllTextToNull(), txtOperator.Trim().ChangAllTextToNull());

                //查询今日是否还有未提交的流水
                string whereAgentNo = Agent == null || Agent == "" ? "" : " AND postInfo.AGENT_NO='" + Agent + "'";
                string specialCondtion = " AND RECORD_TIME>='" + DateTime.Now.ToShortDateString() + "'" + " AND RECORD_TIME<'" + DateTime.Now.AddDays(1).ToShortDateString() + "' " + " AND prints.ID IS NULL ";
                int count = new TransactionController().getTransLogs(whereAgentNo + specialCondtion).Count();
                string msg = "";
                if (count > 0) msg = "今日还有未提交流水的维修业务";

                return Json(new { Sql = sql, Success = true, ErrorMsg = msg });
            }
            catch (Exception ex)
            {
                return Json(new { Sql = "", Success = false, ErrorMsg = ex.Message });
            }
        }

        public ActionResult PrintDaily(string sql, string DailySql, DateTime? StartDate, DateTime? EndDate, string agentNo)
        {
            DateTime? ToCreatedDate = EndDate;
            DateTime? FromCreatedDate = StartDate;

            ViewBag.recordDate = FromCreatedDate.ToText(UIConstants.DateFormatString) + "~" + ToCreatedDate.ToText(UIConstants.DateFormatString);
            ViewBag.agent_name = UIUtility.GetCurrentUser().AgentName;

            #region 导出发票报表部分
            var operatorInvoices = getOperatorInvoices(sql, DailySql);
            List<View_Operator_InvoiceInfos> agentOperatorInvoices = null;
            if (operatorInvoices != null && operatorInvoices.Count() > 0)
            {
                agentOperatorInvoices = (from i in operatorInvoices
                                         // where i.AGENT_NO == agentNo
                                         select i).ToList();
                //写入统计日期
                string sumaryDate = "全部";
                if (ToCreatedDate != null && FromCreatedDate != null)
                {
                    sumaryDate = string.Format("{0}~{1}", FromCreatedDate.ToText(UIConstants.DateFormatString), ToCreatedDate.ToText(UIConstants.DateFormatString));
                }
                else
                {
                    sumaryDate = ToCreatedDate.HasValue ? ToCreatedDate.ToText(UIConstants.DateFormatString) : FromCreatedDate.ToText(UIConstants.DateFormatString);
                }
                if (sumaryDate.IsNullOrEmpty())
                {
                    sumaryDate = DateTime.Now.ToShortDateString() + "~" + DateTime.Now.ToShortDateString();
                }
                ViewBag.recordDate = sumaryDate;
                if (agentOperatorInvoices.Count > 0)
                {


                    ViewBag.agent_name = agentOperatorInvoices[0].AGENT_NAME; //营业厅名称


                    View_Operator_InvoiceInfos ivInfo = agentOperatorInvoices.FirstOrDefault(iv => iv.FeeType == "维修费");
                    if (ivInfo == null)
                    {
                        ivInfo = new View_Operator_InvoiceInfos
                        {
                            AGENT_NAME = agentOperatorInvoices[0].AGENT_NAME,
                            FeeType = "维修费"
                        };
                        agentOperatorInvoices.Add(ivInfo);
                    }

                    ivInfo = agentOperatorInvoices.FirstOrDefault(iv => iv.FeeType == "服务费");
                    if (ivInfo == null)
                    {
                        ivInfo = new View_Operator_InvoiceInfos
                        {
                            AGENT_NAME = agentOperatorInvoices[0].AGENT_NAME,
                            FeeType = "服务费"
                        };
                        agentOperatorInvoices.Add(ivInfo);
                    }

                    ivInfo = agentOperatorInvoices.FirstOrDefault(iv => iv.FeeType == "押金");
                    if (ivInfo == null)
                    {
                        ivInfo = new View_Operator_InvoiceInfos
                        {
                            AGENT_NAME = agentOperatorInvoices[0].AGENT_NAME,
                            FeeType = "押金"
                        };
                        agentOperatorInvoices.Add(ivInfo);
                    }
                    //var agentNew=agentOperatorInvoices.GroupBy(a=>a.FeeType).ToList();
                    List<View_Operator_InvoiceInfos> sumAgentOperatorinoivce = new List<View_Operator_InvoiceInfos>();

                    var typeCount = agentOperatorInvoices.Select(a => a.FeeType).Distinct();
                    for (int i = 0; i < typeCount.Count(); i++)
                    {

                        View_Operator_InvoiceInfos v = new View_Operator_InvoiceInfos();
                        switch (typeCount.ElementAt(i))
                        {
                            case "维修费":
                                var agentNew = agentOperatorInvoices.Where(w => w.FeeType.Equals("维修费")).ToList();
                                v.AGENT_NAME = agentNew.FirstOrDefault().AGENT_NAME;
                                v.AGENT_NO = agentNew.FirstOrDefault().AGENT_NO;
                                v.FeeType = "维修费";
                                v.FUND_AMOUNT = agentNew.Sum(b => b.FUND_AMOUNT);
                                v.INVOICE_TYPE = agentNew.FirstOrDefault().INVOICE_TYPE;
                                v.InvoiceCount = agentNew.Sum(b => b.InvoiceCount);
                                v.Amount = agentNew.Sum(b => b.Amount);
                                break;
                            case "服务费":
                                var agentService = agentOperatorInvoices.Where(w => w.FeeType.Equals("服务费")).ToList();
                                v.AGENT_NAME = agentService.FirstOrDefault().AGENT_NAME;
                                v.AGENT_NO = agentService.FirstOrDefault().AGENT_NO;
                                v.FeeType = "服务费";
                                v.FUND_AMOUNT = agentService.Sum(b => b.FUND_AMOUNT);
                                v.INVOICE_TYPE = agentService.FirstOrDefault().INVOICE_TYPE;
                                v.InvoiceCount = agentService.Sum(b => b.InvoiceCount);
                                v.Amount = agentService.Sum(b => b.Amount);
                                break;
                            case "押金":
                                var agentYajin = agentOperatorInvoices.Where(w => w.FeeType.Equals("押金")).ToList();
                                v.AGENT_NAME = agentYajin.FirstOrDefault().AGENT_NAME;
                                v.AGENT_NO = agentYajin.FirstOrDefault().AGENT_NO;
                                v.FeeType = "押金";
                                v.FUND_AMOUNT = agentYajin.Sum(b => b.FUND_AMOUNT);
                                v.INVOICE_TYPE = agentYajin.FirstOrDefault().INVOICE_TYPE;
                                v.InvoiceCount = agentYajin.Sum(b => b.InvoiceCount);
                                v.Amount = agentYajin.Sum(b => b.Amount);
                                break;
                        }
                        sumAgentOperatorinoivce.Add(v);
                    }
                    agentOperatorInvoices = sumAgentOperatorinoivce;
                }

            }
            #endregion
            #region 导出收退款的报表
            var moneyItems = getOperatorReceivedReturnedMoney(sql, DailySql);

            var agentMoneyItems = (from m in moneyItems
                                   // where m.AGENT_NO == agentNo
                                   select m).ToList();

            decimal from_0 = 0; ;
            decimal from_1 = 0;
            decimal from_2 = 0;
            decimal from_3 = 0;
            decimal from_4 = 0;
            decimal from_5 = 0;
            decimal from_10 = 0;
            decimal all_amount = 0;
            decimal ReturnedAmount = 0;
            decimal ReturnToFinance = 0;
            decimal ReturnedAllAmount = 0;

            if (moneyItems.Count() > 0)
            {
                var agentMoneyGroup = from m in agentMoneyItems
                                      group m by m.AGENT_NO into g
                                      select g;

                agentMoneyItems.Each(v =>
                {
                    from_0 += Convert.ToDecimal(v.from_0);
                    from_1 += Convert.ToDecimal(v.from_1);
                    from_2 += Convert.ToDecimal(v.from_2);
                    from_3 += Convert.ToDecimal(v.from_3);
                    from_4 += Convert.ToDecimal(v.from_4);
                    from_5 += Convert.ToDecimal(v.from_5);
                    from_10 += Convert.ToDecimal(v.from_10);
                    all_amount += Convert.ToDecimal(v.AmountAll);
                    ReturnedAmount += Convert.ToDecimal(v.ReturnedAmount);
                    ReturnToFinance += Convert.ToDecimal(v.ReturnToFinance);
                    ReturnedAllAmount += Convert.ToDecimal(v.ReturnedAllAmount);

                });
            }
            ViewBag.from_0 = from_0;
            ViewBag.from_1 = from_1;
            ViewBag.from_2 = from_2;
            ViewBag.from_3 = from_3;
            ViewBag.from_4 = from_4;
            ViewBag.from_5 = from_5;
            ViewBag.from_10 = from_10;
            ViewBag.AllAmount = all_amount;
            ViewBag.returnAmount = ReturnedAmount;
            ViewBag.returnFinace = ReturnToFinance;
            ViewBag.returnAll = ReturnedAllAmount;
            ViewBag.Operator = UIUtility.GetCurrentUser().OPERATOR_NAME;
            #endregion

            return View(agentOperatorInvoices);


        }
        #endregion

        #region  导出Excel
        public ActionResult ExportDaily(string sql, string searchSql, DateTime? FromCreatedDate, DateTime? ToCreatedDate)
        {
            string exportFileTitle = "营业厅维修日报.xls";
            ActionResult result = null;
            try
            {
                //根据编号判断查询权限//如果营业厅的权限为所有 
                string whereAgentNo = Request["agentNO"] == null || Request["agentNO"].ToString() == "" ? "" : Request["agentNO"].ToString();
                string template = "";
                ExportHelper export = null;
                OPERATOR u = UIUtility.GetCurrentUser();
                //u.AgentNo = "110100010002";
                if (whereAgentNo == "") //如果是速通登录 todo:
                {

                    template = Server.MapPath("/Templates/速通日报.xls");
                    export = new ExportHelper(template);

                    //导出第一个Sheet
                    exportDailyReport(export, sql, searchSql, FromCreatedDate, ToCreatedDate, 0);

                    //导出第2个Sheet
                    exportDailyReport(export, sql, searchSql, FromCreatedDate, ToCreatedDate, 1);

                }
                else //非速通，则导出营业厅报表
                {
                    template = Server.MapPath("/Templates/营业厅日报.xls");
                    export = new ExportHelper(template);
                    exportAgentDailyReport(export, sql, searchSql, FromCreatedDate, ToCreatedDate, u.AgentNo);
                }

                result = export.DownLoad(this.File, exportFileTitle);
            }
            catch (Exception ex)
            {
                result = ExportHelper.HandleExportException(this, ex, exportFileTitle);
            }
            return result;
        }

        /// <summary>
        /// 导出速通日报的第一个Sheet
        /// </summary>
        /// <param name="export"></param>
        /// <param name="sql"></param>
        /// <param name="searchSql"></param>
        /// <param name="FromCreatedDate"></param>
        /// <param name="ToCreatedDate"></param>
        private void exportDailyReport(ExportHelper export, string sql, string searchSql, DateTime? FromCreatedDate, DateTime? ToCreatedDate, int sheetIndex)
        {

            int startRowIndex = 3;

            int printDateIndex = sheetIndex == 0 ? 9 : 11;

            if (sheetIndex == 0) //导出发票报表的Sheet
            {
                var operatorInvoices = getOperatorInvoices(sql, searchSql).ToList();
                export.LastRowIndex = startRowIndex;
                if (operatorInvoices.Count > 0)
                {
                    //将合计行往后移动数据总行数一1
                    export.ExportAddionalInfomation((excel, ep) =>
                    {
                        excel.ActiveSheetIndex = sheetIndex;
                        excel.ShiftRows(4, 5, operatorInvoices.Count() - 1);
                    });

                    //导出主要数据
                    export.ExportToExcel(operatorInvoices, (item, excel, rowIndex) =>
                    {
                        int colIndex = 0;

                        excel[rowIndex, colIndex++] = item.RECORD_TIME.ToText(UIConstants.DateFormatString);
                        excel[rowIndex, colIndex++] = item.AGENT_NAME.ToText();
                        excel[rowIndex, colIndex++] = item.OPERATOR_NAME.ToText();
                        excel[rowIndex, colIndex++] = item.FeeType.ToText();
                        excel.SetCellValue(rowIndex, colIndex++, item.FUND_AMOUNT);
                        excel.SetCellValue(rowIndex, colIndex++, item.StatetaxCount);
                        excel.SetCellValue(rowIndex, colIndex++, item.StatetaxAmount);
                        excel.SetCellValue(rowIndex, colIndex++, item.LocaltaxCount);
                        excel.SetCellValue(rowIndex, colIndex++, item.LocaltaxAmount);
                        excel.SetCellValue(rowIndex, colIndex++, item.ReceipttaxCount);
                        excel.SetCellValue(rowIndex, colIndex++, item.ReceipttaxAmount);

                        //设置样式
                        for (int c = 0; c < colIndex; c++)
                        {
                            excel.SetCellStyle(startRowIndex, c, rowIndex, c);
                        }

                    }, sheetIndex, startRowIndex);

                }
            }
            else //导出收退款的报表
            {
                var moneyItems = getOperatorReceivedReturnedMoney(sql, searchSql).ToList();
                export.LastRowIndex = startRowIndex;
                if (moneyItems.Count > 0)
                {



                    //将合计行往后移动数据总行数一1
                    export.ExportAddionalInfomation((excel, ep) =>
                    {
                        excel.ActiveSheetIndex = sheetIndex;
                        excel.ShiftRows(4, 5, moneyItems.Count() - 1);
                    });

                    //导出主要数据
                    export.ExportToExcel(moneyItems, (item, excel, rowIndex) =>
                    {
                        int colIndex = 0;

                        excel[rowIndex, colIndex++] = item.RECORD_TIME.ToText(UIConstants.DateFormatString);
                        excel[rowIndex, colIndex++] = item.AGENT_NAME.ToText();
                        excel[rowIndex, colIndex++] = item.OPERATOR_NAME.ToText();
                        excel.SetCellValue(rowIndex, colIndex++, item.from_0);
                        excel.SetCellValue(rowIndex, colIndex++, item.from_1);
                        excel.SetCellValue(rowIndex, colIndex++, item.from_2);
                        excel.SetCellValue(rowIndex, colIndex++, item.from_3);
                        excel.SetCellValue(rowIndex, colIndex++, item.from_4);
                        excel.SetCellValue(rowIndex, colIndex++, item.from_5);
                        excel.SetCellValue(rowIndex, colIndex++, item.from_10);
                        excel.SetCellValue(rowIndex, colIndex++, item.AmountAll);
                        excel.SetCellValue(rowIndex, colIndex++, item.ReturnedAmount);
                        excel.SetCellValue(rowIndex, colIndex++, item.ReturnToFinance);
                        excel.SetCellValue(rowIndex, colIndex++, item.ReturnedAllAmount);

                        //设置样式
                        for (int c = 0; c < colIndex; c++)
                        {
                            excel.SetCellStyle(startRowIndex, c, rowIndex, c);
                        }

                    }, sheetIndex, startRowIndex);
                }
            }

            //导出合计，日期等其它的信息
            export.ExportAddionalInfomation((excel, ep) =>
            {
                excel.ActiveSheetIndex = sheetIndex;
                //写入统计日期
                string sumaryDate = "全部";
                if (ToCreatedDate != null && FromCreatedDate != null)
                {
                    sumaryDate = string.Format("{0}~{1}", FromCreatedDate.ToText(UIConstants.DateFormatString), ToCreatedDate.ToText(UIConstants.DateFormatString));
                }
                else
                {
                    sumaryDate = ToCreatedDate.HasValue ? ToCreatedDate.ToText(UIConstants.DateFormatString) : FromCreatedDate.ToText(UIConstants.DateFormatString);
                }
                if (sumaryDate.IsNullOrEmpty())
                {
                    sumaryDate = DateTime.Now.ToShortDateString() + "~" + DateTime.Now.ToShortDateString();
                }
                excel[1, 1] = sumaryDate;

                int startColIndex = sheetIndex == 0 ? 4 : 3;
                int endColIndex = sheetIndex == 0 ? 10 : 12;

                //写入合计信息
                for (int i = startColIndex; i <= endColIndex; i++)
                {
                    Char c = ('A'.CharToAscii() + i).AsciiToChar();

                    string formula = string.Format("SUM({0}{1}:{0}{2})", c, startRowIndex + 1, ep.LastRowIndex + 1);
                    excel.SetCellFormula(ep.LastRowIndex + 1, i, formula);
                }
                //excel[++ep.LastRowIndex, 0] = "合计：";
                ep.LastRowIndex += 2;
                //写入打印时间
                //excel[++ep.LastRowIndex, printDateIndex] = "打印时间：";
                if (sheetIndex == 0)
                {
                    excel[ep.LastRowIndex, printDateIndex + 1] = DateTime.Now.ToString(UIConstants.DateFormatString);
                }
                else
                {
                    excel[ep.LastRowIndex, printDateIndex + 2] = DateTime.Now.ToString(UIConstants.DateFormatString);
                }

            });
        }


        /// <summary>
        /// 导出营业厅日报
        /// </summary>
        /// <param name="export"></param>
        /// <param name="sql"></param>
        /// <param name="searchSql"></param>
        /// <param name="FromCreatedDate"></param>
        /// <param name="ToCreatedDate"></param>
        private void exportAgentDailyReport(ExportHelper export, string sql, string searchSql, DateTime? FromCreatedDate, DateTime? ToCreatedDate, string agentNo)
        {


            int startRowIndex = 3; //起始列

            int printDateIndex = 9;//打印日期列

            string agentName = ""; //营业厅

            #region 导出发票报表部分
            var operatorInvoices = getOperatorInvoices(sql, searchSql);
            if (operatorInvoices != null && operatorInvoices.Count() > 0)
            {
                var agentOperatorInvoices = (from i in operatorInvoices
                                             where i.AGENT_NO == agentNo
                                             select i).ToList();

                if (agentOperatorInvoices.Count > 0)
                {

                    agentName = agentOperatorInvoices[0].AGENT_NAME; //营业厅名称


                    View_Operator_InvoiceInfos ivInfo = agentOperatorInvoices.FirstOrDefault(iv => iv.FeeType == "维修费");
                    if (ivInfo == null)
                    {
                        ivInfo = new View_Operator_InvoiceInfos
                        {
                            AGENT_NAME = agentName,
                            FeeType = "维修费"
                        };
                        agentOperatorInvoices.Add(ivInfo);
                    }

                    ivInfo = agentOperatorInvoices.FirstOrDefault(iv => iv.FeeType == "服务费");
                    if (ivInfo == null)
                    {
                        ivInfo = new View_Operator_InvoiceInfos
                        {
                            AGENT_NAME = agentName,
                            FeeType = "服务费"
                        };
                        agentOperatorInvoices.Add(ivInfo);
                    }

                    ivInfo = agentOperatorInvoices.FirstOrDefault(iv => iv.FeeType == "押金");
                    if (ivInfo == null)
                    {
                        ivInfo = new View_Operator_InvoiceInfos
                        {
                            AGENT_NAME = agentName,
                            FeeType = "押金"
                        };
                        agentOperatorInvoices.Add(ivInfo);
                    }

                    var agentInvoiceGroup = (from i in agentOperatorInvoices
                                             group i by i.FeeType into g
                                             select g).ToList();



                    //导出主要数据
                    export.ExportToExcel(agentInvoiceGroup, (item, excel, rowIndex) =>
                    {
                        int colIndex = 0;

                        if (!item.Key.IsNullOrEmpty())
                        {
                            excel[rowIndex, colIndex] = item.Key.ToText();
                            colIndex = 4;
                            excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.Amount));
                            excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.StatetaxCount));
                            excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.StatetaxAmount));
                            excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.LocaltaxCount));
                            excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.LocaltaxAmount));
                            excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.ReceipttaxCount));
                            excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.ReceipttaxAmount));
                        }
                    }, 0, startRowIndex);
                }
            }
            else
            {
                Content("<script>alert('数据为空，无法操作导出')</script>");
            }

            #endregion



            #region 导出收退款的报表
            var moneyItems = getOperatorReceivedReturnedMoney(sql, searchSql);

            var agentMoneyItems = (from m in moneyItems
                                   where m.AGENT_NO == agentNo
                                   select m).ToList();

            if (moneyItems.Count() > 0)
            {
                var agentMoneyGroup = from m in agentMoneyItems
                                      group m by m.AGENT_NO into g
                                      select g;

                //导出主要数据
                export.ExportToExcel(agentMoneyGroup, (item, excel, rowIndex) =>
                {
                    int colIndex = 0;
                    rowIndex = 7;


                    excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.from_0));
                    excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.from_1));
                    excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.from_2));
                    excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.from_3));
                    excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.from_4));
                    excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.from_5));
                    excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.from_10));
                    excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.AmountAll));
                    excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.ReturnedAmount));
                    excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.ReturnToFinance));
                    excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.ReturnedAllAmount));
                }, 0, startRowIndex);
            }
            #endregion



            //导出合计，日期等其它的信息
            export.ExportAddionalInfomation((excel, ep) =>
            {
                //写入统计日期
                string sumaryDate = "全部";
                if (ToCreatedDate != null && FromCreatedDate != null)
                {
                    sumaryDate = string.Format("{0}~{1}", FromCreatedDate.ToText(UIConstants.DateFormatString), ToCreatedDate.ToText(UIConstants.DateFormatString));
                }
                else
                {
                    sumaryDate = ToCreatedDate.HasValue ? ToCreatedDate.ToText(UIConstants.DateFormatString) : FromCreatedDate.ToText(UIConstants.DateFormatString);
                }
                if (sumaryDate.IsNullOrEmpty())
                {
                    sumaryDate = DateTime.Now.ToShortDateString() + "~" + DateTime.Now.ToShortDateString();
                }
                excel[1, 1] = sumaryDate;

                //写入营业厅                
                excel[1, 4] = agentName;
                //写入操作员
                excel[1, 7] = UIUtility.GetCurrentUser().OPERATOR_NAME;

                //写入打印时间
                excel[8, printDateIndex] = "打印时间：";
                excel[8, printDateIndex + 1] = DateTime.Now.ToString(UIConstants.DateFormatString);

            });
        }

        #endregion

        #region 发票报表

        public ActionResult Invoice()
        {
            return View();
        }


        /// <summary>
        /// 根据维修单状态，返回维修单，为空则返回全部
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [GridAction]
        public ActionResult ListInvoiceSqlItems(string sql, string searchSql)
        {

            //根据编号判断查询权限
            string whereAgentNo = Request["agentNO"] == null || Request["agentNO"].ToString() == "" ? "" : " AND AGENT_NO='" + Request["agentNO"].ToString() + "'";
            string modelID = Request["menuNames"] == null || Request["menuNames"].ToString() == "" ? "" : Request["menuNames"].ToString();
            //如果权限为本营业厅操作员权限 则增加登录员查询条件
            OPERATOR operatorInfo = UIUtility.GetCurrentUser();
            string models = UIUtility.getOperatorByModule(operatorInfo, Convert.ToInt32(modelID));
            string strOperator = "";
            if (models == "6")
                strOperator = " AND OPERATOR_NO='" + UIUtility.GetCurrentUser().OPERATOR_NO + "' ";

            string tempSql = "";
            if ((!sql.IsNullOrEmpty()) && (!searchSql.IsNullOrEmpty()))
                tempSql = sql + " and " + searchSql;
            else
                tempSql = string.Format("{0} {1}", sql, searchSql);

            string normalSql = tempSql.Trim() == "" ?
              " [INCOICE_TIME]>='" + DateTime.Now.ToShortDateString() + "' AND [INCOICE_TIME] <'" + DateTime.Now.AddDays(1).ToShortDateString() + "' " :
              "";//如果没有任何条件 则默认显示当天的所有信息

            var result = obuMgr.GetInvoices(tempSql + normalSql + whereAgentNo + strOperator);
            var temp = new GridModel(result);
            return View(temp);
        }

        [HttpPost]
        public ActionResult InvoiceSearch(DateTime? FromCreatedDate, DateTime? ToCreatedDate, string InvoiceType,
            string UserNo, string SERIAL_NO, string Operator, string AgentType, string Agent, string txtOperator) //也可以用这种格式接收参数public ActionResult Search(FormCollection form)
        {
            try
            {
                var sql = obuMgr.GetSearhInvoiceReportSql(FromCreatedDate, ToCreatedDate,
                    InvoiceType.ChangAllTextToNull(), UserNo.ChangAllTextToNull(), SERIAL_NO.ChangAllTextToNull(),
                    Operator.ChangAllTextToNull(), AgentType.ChangAllTextToNull(), Agent.ChangAllTextToNull(), txtOperator.Trim().ChangAllTextToNull());

                return Json(new { Sql = sql, Success = true, ErrorMsg = "" });
            }
            catch (Exception ex)
            {
                return Json(new { Sql = "", Success = false, ErrorMsg = ex.Message });
            }
        }


        #endregion

        #region 交易资金来源报表 Transaction

        public ActionResult Transaction()
        {
            return View();
        }


        /// <summary>
        /// 根据维修单状态，返回维修单，为空则返回全部
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [GridAction]
        public ActionResult ListTransactionSqlItems(string sql, string searchSql)
        {
            //根据编号判断查询权限
            string whereAgentNo = Request["agentNO"] == null || Request["agentNO"].ToString() == "" ? "" : " AND AGENT_NO='" + Request["agentNO"].ToString() + "'";
            string modelID = Request["menuNames"] == null || Request["menuNames"].ToString() == "" ? "" : Request["menuNames"].ToString();
            //如果权限为本营业厅操作员权限 则增加登录员查询条件
            OPERATOR operatorInfo = UIUtility.GetCurrentUser();
            string models = UIUtility.getOperatorByModule(operatorInfo, Convert.ToInt32(modelID));
            string strOperator = "";
            if (models == "6")
                strOperator = " AND tLogOperator='" + UIUtility.GetCurrentUser().OPERATOR_NO + "' ";

            string tempSql = "";
            if ((!sql.IsNullOrEmpty()) && (!searchSql.IsNullOrEmpty()))
                tempSql = sql + " and " + searchSql;
            else
                tempSql = string.Format("{0} {1}", sql, searchSql);

            string normalSql = tempSql.Trim() == "" ?
                " RECORD_TIME>='" + DateTime.Now.ToShortDateString() + "' AND RECORD_TIME <'" + DateTime.Now.AddDays(1).ToShortDateString() + "' " :
                "";//如果没有任何条件 则默认显示当天的所有信息
            var result = obuMgr.GetTransactions(tempSql + normalSql + whereAgentNo + strOperator).ToList();
            try
            {


                //获取流水单ID
                var serialids = (from i in result
                                 select i.SERIAL_ID).ToArray();
                //根据流水单ID获取资金来源信息
                var fundDetailResult = (from i in new OBU_CAREContext().FUND_DETAIL.Include("FUND_INFO")
                                        where i.FUND_INFO.ID == i.FUND_ID && serialids.Contains(i.SERIAL_ID)
                                        select i).ToList();
                //将资金来源信息合并
                result.Each(r =>
                {
                    if (r.SERIAL_ID != null && r.SERIAL_ID.ToText() != "")
                    {
                        var fund = (from i in fundDetailResult
                                    where i.SERIAL_ID == long.Parse(r.SERIAL_ID.ToString())
                                    select i).ToList();
                        string fundInfo = "";
                        string fundremark = "";
                        fund.Each(f =>
                        {
                            fundInfo += new ConfigMgr().GetTypeValue(f.FUND_INFO.FUND_FROM, ConfigMgr.tblFundType).DICT_TITLE + ":" + f.FUND_INFO.AMOUNT + " ";
                            fundremark += f.FUND_INFO.REMARK + " ";
                        });
                        r.FUNDINFO = fundInfo;
                        r.FUND_REMARK = fundremark;
                    }
                });

                var temp = new GridModel(result);
                return View(temp);

            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        [HttpPost]
        public ActionResult TransactionSearch(DateTime? FromCreatedDate, DateTime? ToCreatedDate, string TransactionType,
            string UserNo, string SERIAL_NO, string Operator, string AgentType, string Agent, string txtOperator) //也可以用这种格式接收参数public ActionResult Search(FormCollection form)
        {
            try
            {
                var sql = obuMgr.GetSearhTransactionReportSql(FromCreatedDate, ToCreatedDate,
                    TransactionType.ChangAllTextToNull(), UserNo.Trim().ChangAllTextToNull(),
                    SERIAL_NO.Trim().ChangAllTextToNull(), Operator.ChangAllTextToNull(), AgentType.ChangAllTextToNull(),
                    Agent.ChangAllTextToNull(), txtOperator.Trim().ChangAllTextToNull());

                return Json(new { Sql = sql, Success = true, ErrorMsg = "" });
            }
            catch (Exception ex)
            {
                return Json(new { Sql = "", Success = false, ErrorMsg = ex.Message });
            }
        }


        #endregion

        #region 厂商报价查询报表
        public ActionResult SupplierPriceReport()
        {
            return View();
        }
        [GridAction]
        public ActionResult ListSupplierPrice(string sql, string searchSql)
        {
            var result = getList(sql, searchSql);
            var temp = new GridModel(result);
            return View(temp);
        }
        /// <summary>
        /// 获取结果集
        /// </summary>
        /// <param name="sDate"></param>
        /// <param name="eDate"></param>
        /// <param name="condtion"></param>
        /// <returns></returns>
        public IEnumerable<SupplierPriceViewModel> getList(string sql, string searchSql)
        {
            string sqlContent = "select a.SUPPLIER_ID,SHORTNAME,min(sumprice) MIN_PRICE,max(sumprice)MAX_PRICE,"
                + "CONVERT(DECIMAL(8,2),sum(SUMPRICE)/count(1)) AVG_PRICE,sum(sumprice) SUM_PRICE,"
                + " count(1)SUM_COUNT from ("
                + " SELECT b.supplier_id,b.repair_order_id,E.SHORTNAME,sum(price) sumprice FROM ("
                + " SELECT DISTINCT REPAIR_ORDER_ID  FROM REPAIR_ORDER_TRACKING "
                + " WHERE REPAIR_ORDER_STATUS IN (10,11,12,13,14) " + searchSql + ") AS A "
                + " INNER JOIN REPAIR_ORDER_PRICE AS B ON A.REPAIR_ORDER_id=b.repair_order_id "
                + " inner join repair_orders as c on b.repair_order_id=c.id "
                + " INNER JOIN SUPPLIERS AS E ON B.SUPPLIER_ID=E.ID "
                + " where c.status!=-1 and price_flag!='0' " + sql
                + " group by b.supplier_id,b.repair_order_Id,E.SHORTNAME "
                + " ) as a "
                + " group by supplier_id,SHORTNAME";
            return db.Database.SqlQuery<SupplierPriceViewModel>(sqlContent);
        }

        [HttpPost]
        public JsonResult SupplierPriceSearch(DateTime? sDate, DateTime? eDate, string Supplier)
        {
            try
            {
                string sql = "";
                if (Supplier != "全部")
                    sql = " AND B.SUPPLIER_ID=" + Supplier;

                string searchSql = "";
                if (sDate != null && eDate != null)
                    searchSql = " AND CREATED_DATE>='" + sDate.Value + "' AND CREATED_DATE<'" + eDate.Value.AddDays(1) + "'";

                return Json(new { Sql = sql, SearchSql = searchSql, Success = true, ErrorMsg = "" });
            }
            catch (Exception ex)
            {
                return Json(new { Sql = "", SearchSql = "", Success = false, ErrorMsg = ex.Message });
            }
        }
        #endregion

        #region 厂商故障点分类汇总统计
        public ActionResult SupplierFault()
        {
            return View();
        }
        public ActionResult ListSupplierFault(DateTime? sDate, DateTime? eDate)
        {
            if (sDate == null || eDate == null)
                return Content("<Script>alert('请输入完整日期');window.location.href='/Report/SupplierFault/';</Script>");
            DataTable result = getSupplierFault(sDate, eDate);

            return excelCheckOut(result, sDate, eDate);
        }
        public DataTable getSupplierFault(DateTime? sDate, DateTime? eDate)
        {
            string sql = "";
            string searchSql = "";

            //if (Supplier != "全部")
            //    sql = " AND  B.SUPPLIER_ID=" + Supplier;
            searchSql = " AND CREATED_DATE>='" + sDate.Value + "' AND CREATED_DATE<'" + eDate.Value.AddDays(1) + "' ";

            var suppliers = db.SUPPLIERS.ToList();
            string supplierContent = "";
            foreach (var item in suppliers)
            {
                supplierContent += "COUNT(CASE WHEN B.SUPPLIER_ID=" + item.ID + " THEN 1 END )" + item.SHORTNAME + ",";
            }
            supplierContent = supplierContent.Substring(0, supplierContent.Length - 1);
            string sqlContent = "SELECT  PARTS_NAME '元器件', " + supplierContent
                + "  FROM ( "
                + "  SELECT DISTINCT REPAIR_ORDER_ID  FROM REPAIR_ORDER_TRACKING WHERE REPAIR_ORDER_STATUS IN (10,11,12,13,14) " + searchSql
                + "  )AS A INNER JOIN REPAIR_ORDER_PRICE AS B ON A.REPAIR_ORDER_id=b.repair_order_id "
                + "  inner join repair_orders as c on b.repair_order_id=c.id "
                + " INNER JOIN PARTS_TYPE AS D ON B.FAULT_PARTS=D.ID "
                + " where c.status!=-1 " + sql
                + " GROUP BY D.PARTS_NAME ";
            return new DatabaseHelper().ExecuteSqlDataSet(sqlContent).Tables[0];
        }
        /// <summary>
        /// 导出厂商故障点数据
        /// </summary>
        /// <param name="export"></param>
        /// <param name="sql"></param>
        /// <param name="searchSql"></param>
        /// <param name="FromCreatedDate"></param>
        /// <param name="ToCreatedDate"></param>
        private ActionResult excelCheckOut(DataTable dt, DateTime? sDate, DateTime? eDate)
        {
            string exportFileTitle = "厂商故障分类统计.xls";
            string template = Server.MapPath("/Templates/厂商故障分类统计.xls");
            ExportHelper export = new ExportHelper(template);
            int startRowIndex = 3; //主要数据起始行
            int startColIndex = 0; //主要数据起始列

            //导出主要数据
            export.ExportToExcel(dt.Rows.Cast<DataRow>(), (item, excel, rowIndex) =>
            {

                int i = 0;
                for (; i < item.Table.Columns.Count; i++)
                {
                    if (i == startColIndex) //第一列写配件名
                    {
                        excel[rowIndex, i] = item[i].ToText();
                    }
                    else //其它列写数字
                    {
                        excel.SetCellValue(rowIndex, i, item[i].ToDouble());
                    }
                }


                //写入最后一列的合计信息
                Char c1 = ('A'.CharToAscii() + startColIndex + 1).AsciiToChar();
                Char c2 = ('A'.CharToAscii() + i - 1).AsciiToChar();
                string formula = string.Format("SUM({0}{1}:{2}{1})", c1, rowIndex + 1, c2);
                excel.SetCellFormula(rowIndex, i, formula);


                //设置样式
                for (int col = startColIndex; col <= i; col++)
                {
                    excel.SetCellStyle(startRowIndex, startColIndex, rowIndex, col);
                }

            }, 0, startRowIndex);


            //导出合计，日期等其它的信息
            export.ExportAddionalInfomation((excel, ep) =>
            {
                //合并标题的单元格式
                excel.Merge(0, 0, 0, dt.Columns.Count + 1);

                //写入统计日期
                excel[1, 1] = DateTime.Now.ToString(UIConstants.DateFormatString);

                //设置表头
                int i = startColIndex + 1;
                for (; i < dt.Columns.Count; i++)
                {
                    excel[startRowIndex - 1, i] = dt.Columns[i].ColumnName;
                }

                //写表头的合计列列头
                excel[startRowIndex - 1, i] = "合计";

                //设置样式
                for (int col = startColIndex + 1; col <= i; col++)
                {
                    excel.SetCellStyle(startRowIndex, startColIndex + 1, startRowIndex - 1, col);
                }

                //写入日期
                excel[1, 1] = sDate.Value.ToShortDateString() + "至" + eDate.Value.ToShortDateString();

                //写入最后一行的合计信息
                excel[ep.LastRowIndex + 1, startColIndex] = "合计：";
                for (int j = startColIndex + 1; j <= dt.Columns.Count; j++)
                {
                    Char c = ('A'.CharToAscii() + j).AsciiToChar();

                    string formula = string.Format("SUM({0}{1}:{0}{2})", c, startRowIndex + 1, ep.LastRowIndex + 1);
                    excel.SetCellFormula(ep.LastRowIndex + 1, j, formula);
                }

                //设置样式
                for (int col = startColIndex; col <= dt.Columns.Count; col++)
                {
                    excel.SetCellStyle(startRowIndex, startColIndex, ep.LastRowIndex + 1, col);
                }

            });

            return export.DownLoad(this.File, exportFileTitle);
        }


        #endregion

        #region 厂商维修结账统计
        public ActionResult SupplierRepairReport()
        {
            return View();
        }
        [GridAction]
        public ActionResult ListSupplierRepair(string sql, string searchSql)
        {
            var result = getResult(sql, searchSql);
            var temp = new GridModel(result);
            return View(temp);
        }
        /// <summary>
        /// 获取厂商维修结账结果集
        /// </summary>
        /// <returns></returns>
        public List<SupplierRepairViewModel> getResult(string sql, string searchSql)
        {
            DatabaseHelper dbH = new DatabaseHelper();
            string sqlContent = "SELECT D.AGENT_NAME,C.SHORTNAME,B.SPEC_NAME,B.OBU_NO,B.ID,SUM(R.SUPPLIER_PRICE) PRICE  FROM (SELECT DISTINCT REPAIR_ORDER_ID FROM REPAIR_ORDER_TRACKING "
            + " WHERE REPAIR_ORDER_STATUS=" + RepairOrderStatus.ClosedWithCustomerPickedBack.ToInt().Value + sql
            + " )AS A "
            + " INNER JOIN REPAIR_ORDERS AS B ON A.REPAIR_ORDER_ID=B.ID "
            + " INNER JOIN SUPPLIERS AS C ON B.SUPPLIER_ID=C.ID "
            + " INNER JOIN (SELECT AGENT_NO,AGENT_NAME,AGENT_TYPE_NO FROM ISSUE_RPT.DBO.ISSUER_AGENTS "
            + " UNION ALL SELECT ISSUER_NO,ISSUER_NAME,'' AGENT_TYPE_NO FROM ISSUE_RPT.DBO.ISSUERS) "
            + " AS D ON B.AGENT_NO=D.AGENT_NO "
            + " INNER JOIN REPAIR_ORDER_PRICE AS R ON A.REPAIR_ORDER_ID=R.REPAIR_ORDER_ID"
            + " WHERE B.STATUS!=-1 AND R.PRICE_FLAG!='N' AND R.PRICE_FLAG!='0' " + searchSql
            + " AND CONFIRM_REPAIRED=" + ConfirmRepairedStatus.Repair.ToInt().Value
            + " GROUP BY  AGENT_NAME,C.SHORTNAME,B.SPEC_NAME,B.OBU_NO,B.ID";
            DataTable dt = new DatabaseHelper().ExecuteSqlDataSet(sqlContent).Tables[0];
            List<SupplierRepairViewModel> result = new List<SupplierRepairViewModel>();
            foreach (DataRow item in dt.Rows)
            {
                SupplierRepairViewModel s = new SupplierRepairViewModel();
                s.agentName = item["AGENT_NAME"].ToString();
                s.shortName = item["SHORTNAME"].ToString();
                s.specName = item["SPEC_NAME"].ToString();
                s.obuNO = item["OBU_NO"].ToString();
                s.faultRemark = getFaultRemark(item["ID"].ToString(), dbH);
                s.sumPrice = Convert.ToDecimal(item["PRICE"].ToString());
                result.Add(s);
            }
            return result;
        }
        /// <summary>
        /// 获取维修单的故障点字符串
        /// </summary>
        /// <param name="repairOrderID"></param>
        /// <returns></returns>
        public string getFaultRemark(string repairOrderID, DatabaseHelper db)
        {
            string sqlContent = " SELECT A.REPAIR_ORDER_ID,A.SUPPLIER_PRICE,B.PARTS_NAME FROM REPAIR_ORDER_PRICE AS A "
                + " INNER JOIN PARTS_TYPE AS B ON A.FAULT_PARTS=B.ID "
                + " WHERE PRICE_FLAG!='0'  AND A.REPAIR_ORDER_ID=" + repairOrderID;
            DataTable dt = db.ExecuteSqlDataSet(sqlContent).Tables[0];
            string remark = "";
            foreach (DataRow item in dt.Rows)
            {
                remark += item["PARTS_NAME"].ToString() + "、";
            }
            return remark;
        }
        /// <summary>
        /// 获取查询条件
        /// </summary>
        /// <param name="FromCreatedDate"></param>
        /// <param name="ToCreatedDate"></param>
        /// <param name="Agent"></param>
        /// <param name="Supplier"></param>
        /// <param name="ObuNO"></param>
        /// <returns></returns>
        [HttpPost]
        public ActionResult RepairSearch(DateTime? sDate, DateTime? eDate, string Agent, string Supplier, string ObuNO, string flag)
        {
            try
            {
                string sql = "";
                string searchSql = "";
                if (eDate != null && sDate != null)
                    sql = " AND CREATED_DATE>='" + sDate.Value + "' AND CREATED_DATE<'" + eDate.Value.AddDays(1) + "' ";

                if (Agent != "全部")
                    searchSql += " AND B.AGENT_NO='" + Agent.ChangAllTextToNull().Trim() + "'";
                if (Supplier != "全部")
                    searchSql += " AND C.ID=" + Supplier.ChangAllTextToNull();
                if (ObuNO.IsNullOrEmpty() == false)
                    searchSql += " AND B.OBU_NO='" + ObuNO + "'";

                if (flag == "report")
                    return ReportRepair(sql, searchSql, sDate, eDate);
                else
                    return Json(new { Sql = sql, SearchSql = searchSql, Success = true, ErrorMsg = "" });
            }
            catch (Exception ex)
            {
                if (flag == "report")
                    return Content("<script>alert('导出失败！');</script>");
                return Json(new { Sql = "", Success = false, ErrorMsg = ex.Message });
            }
        }

        private ActionResult ReportRepair(string sql, string searchSql, DateTime? sDate, DateTime? eDate)
        {
            //厂商维修结账统计.xls
            List<SupplierRepairViewModel> list = getResult(sql, searchSql);
            if (list.Count > 0)
            {
                string exportFileTitle = "厂商维修结账统计.xls";
                string template = Server.MapPath("/Templates/厂商维修结账统计.xls");
                ExportHelper export = new ExportHelper(template);
                string dateStr = "全部时间";
                if (sDate != null && eDate != null)
                {
                    dateStr = sDate.Value.ToShortDateString() + " 至 " + eDate.Value.ToShortDateString();
                }
                ExportRepair(export, list, dateStr);
                return export.DownLoad(this.File, exportFileTitle);
            }
            else
            {
                return Content("<script>alert('该条件范围内数据不存在！');window.location.href='/Report/SupplierRepairReport/';</script>");
            }
        }

        private void ExportRepair(ExportHelper export, List<SupplierRepairViewModel> list, string dateStr)
        {
            int startRowIndex = 3;//主要数据起始行
            //int startCellIndex = 0;//主要数据起始列
            int sheetIndex = 0;

            if (list.Count > 0)
            {
                export.ExportToExcel(list, (item, excel, rowIndex) =>
                {
                    excel[rowIndex, 0] = item.agentName;
                    excel[rowIndex, 1] = item.shortName;
                    excel[rowIndex, 2] = item.specName;
                    excel[rowIndex, 3] = item.obuNO;
                    excel[rowIndex, 4] = item.faultRemark;
                    excel.SetCellValue(rowIndex, 5, item.sumPrice);
                    for (int col = startRowIndex + 1; col < list.Count + startRowIndex; col++)
                    {
                        excel.SetCellStyle(startRowIndex, 0, col, 0);
                        excel.SetCellStyle(startRowIndex, 1, col, 1);
                        excel.SetCellStyle(startRowIndex, 2, col, 2);
                        excel.SetCellStyle(startRowIndex, 3, col, 3);
                        excel.SetCellStyle(startRowIndex, 4, col, 4);
                        excel.SetCellStyle(startRowIndex, 5, col, 5);
                    }
                }, sheetIndex, startRowIndex);

                export.ExportAddionalInfomation((excel, ep) =>
                {
                    excel[1, 0] = "日期：" + dateStr;
                    excel[ep.LastRowIndex + 1, 0] = "合计：";

                    Char c = ('A'.CharToAscii() + 5).AsciiToChar();

                    string formula = string.Format("SUM({0}{1}:{0}{2})", c, startRowIndex + 1, ep.LastRowIndex + 1);
                    excel.SetCellFormula(ep.LastRowIndex + 1, 5, formula);

                    for (int col = 0; col <= 5; col++)
                    {
                        excel.SetCellStyle(startRowIndex, col, ep.LastRowIndex + 1, col);
                    }
                });
            }
        }
        #endregion

        #region 维护维修日报汇总
        public ActionResult RepairDaily()
        {
            //var result = getResult(searchSql);
            //decimal returnDeposit = 0, collectsDeposit = 0, repairObu = 0, issueSpareTape = 0, issueBase = 0, repairServiceFee = 0, sumPrice = 0;
            //if (result != null && result.Count() > 0)
            //{
            //    foreach (var item in result)
            //    {
            //        returnDeposit += item.ReturnDeposit;
            //        collectsDeposit += item.CollectsDeposit;
            //        repairObu += item.RepairObu;
            //        issueSpareTape += item.IssueSpareTape;
            //        issueBase += item.IssueBase;
            //        repairServiceFee += item.RepairServiceFee;
            //        sumPrice += item.SUM_PRICE;
            //    }
            //}
            //ViewBag.ReturnDeposit = returnDeposit;
            //ViewBag.CollectsDeposit = collectsDeposit;
            //ViewBag.RepairObu = repairObu;
            //ViewBag.IssueSpareTape = issueSpareTape;
            //ViewBag.IssueBase = issueBase;
            //ViewBag.RepairServiceFee = repairServiceFee;
            //ViewBag.SUM_PRICE = sumPrice;
            return View();
        }
        [GridAction]
        public ActionResult ListRepairDaily(string searchSql)
        {
            var result = getResult(searchSql, false);
            var temp = new GridModel(result);
            return View("RepairDaily", temp);
        }
        public ActionResult _viewSum()
        {
            ViewBag.ReturnDeposit = 0;
            return ViewBag.ReturnDeposit;
        }
        public IEnumerable<RepairDailyViewModel> getResult(string searchSql, bool isSelectSum)
        {
            var dictResult = new ConfigMgr().GetTypeValues(ConfigMgr.tblTransactionType);
            string content = "";
            foreach (var item in dictResult)
            {
                content += "ISNULL(SUM(CASE WHEN OPERATION_KIND='" + item.DICT_CODE + "' THEN AMOUNT END),0)" + Enum.Parse(typeof(TransactionType), item.DICT_CODE).ToText() + ",";
            }
            content = content.Substring(0, content.Length - 1);
            string sqlContent = " SELECT " + (isSelectSum ? "" : "CONVERT(DATE,RECORD_TIME) RECORD_DATE ,I.AGENT_NO,I.AGENT_NAME,O.OPERATOR_NO,O.OPERATOR_NAME,") + content
                + " FROM OPERATION_CARE_LOGS  AS A "
                + " INNER JOIN PRINT_DETAIL AS B ON A.ID=B.LOG_ID "
                + " INNER JOIN PRINT_LOG AS C ON C.ID=B.SERIAL_ID "
                + " INNER JOIN ISSUE_RPT.DBO.ISSUER_AGENTS AS I ON C.AGENT_NO=I.AGENT_NO "
                + " INNER JOIN ISSUE_RPT.DBO.OPERATORS AS O ON C.OPERATOR_NO=O.OPERATOR_NO " + searchSql + (searchSql.ToUpper().Contains("WHERE") ? " AND A.STATUS=1 AND C.STATUS=1 " : " WHERE A.STATUS=1 AND C.STATUS=1 ")
                + (isSelectSum ? "" : " GROUP BY CONVERT(DATE,RECORD_TIME), I.AGENT_NO,I.AGENT_NAME,O.OPERATOR_NO,O.OPERATOR_NAME ");
            return db.Database.SqlQuery<RepairDailyViewModel>(sqlContent);
        }

        [HttpPost]
        public ActionResult repairDailySearch(DateTime? FromCreatedDate, DateTime? ToCreatedDate, string Agent, string Operator) //也可以用这种格式接收参数public ActionResult Search(FormCollection form)
        {
            try
            {
                var searchSql = obuMgr.GetRepairDailySearch(FromCreatedDate, ToCreatedDate, Agent.ChangAllTextToNull(),
                                       Operator.ChangAllTextToNull());
                var result = getResult("WHERE " + searchSql, true).ToList()[0];
                decimal returnDeposit = 0, collectsDeposit = 0, repairObu = 0, issueSpareTape = 0, issueBase = 0, repairServiceFee = 0, sumPrice = 0;
                returnDeposit += result.ReturnDeposit;
                collectsDeposit += result.CollectsDeposit;
                repairObu += result.RepairObu;
                issueSpareTape += result.IssueSpareTape;
                issueBase += result.IssueBase;
                repairServiceFee += result.RepairServiceFee;
                sumPrice += result.SUM_PRICE;

                return Json(new { SearchSql = "WHERE " + searchSql, Success = true, ErrorMsg = "", ReturnDeposit = returnDeposit, CollectsDeposit = collectsDeposit, RepairObu = repairObu, IssueSpareTape = issueSpareTape, IssueBase = issueBase, RepairServiceFee = repairServiceFee, SUM_PRICE = sumPrice });
            }
            catch (Exception ex)
            {
                return Json(new { SearchSql = "", Success = false, ErrorMsg = ex.Message });
            }
        }
        #endregion

        #region 厂商维修周期统计
        public ActionResult SupplierRepairTime()
        {
            return View();
        }
        [GridAction]
        public ActionResult ListRepairTime(string searchSql)
        {
            var result = getSupplierRepairTime(searchSql);
            var temp = new GridModel(result);
            return View(temp);
        }
        public IEnumerable<RepairTimeViewModel> getSupplierRepairTime(string searchSql)
        {
            try
            {
                searchSql = searchSql == "" ? "" : " WHERE " + searchSql;
                string sqlContent = "SELECT C.SHORTNAME,COUNT(DISTINCT REPAIR_ORDER_ID) SUM_ORDER,"
                    + " SUM(DATEDIFF ( day  ,B.CREATED_DATE, A.CREATED_DATE  ))/ COUNT(DISTINCT REPAIR_ORDER_ID) AVG_DAY "
                    + " FROM (SELECT DISTINCT REPAIR_ORDER_ID,MAX(CREATED_DATE) CREATED_DATE FROM REPAIR_ORDER_TRACKING " + searchSql
                    + " GROUP BY REPAIR_ORDER_ID) AS A  "
                    + " INNER JOIN REPAIR_ORDERS AS B ON A.REPAIR_ORDER_ID=B.ID "
                    + " INNER JOIN SUPPLIERS AS C ON B.SUPPLIER_ID=C.ID "
                    + " WHERE B.STATUS!=-1 "
                    + " GROUP BY SHORTNAME ";
                return db.Database.SqlQuery<RepairTimeViewModel>(sqlContent);
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }
        public ActionResult RepairTimeSearch(DateTime? FromCreatedDate, DateTime? ToCreatedDate, string Status)
        {
            try
            {
                string sql = "";
                if (FromCreatedDate != null && ToCreatedDate != null)
                    sql = " CREATED_DATE>='" + FromCreatedDate.Value + "' AND CREATED_DATE<'" + ToCreatedDate.Value.AddDays(1) + "' ";
                if (Status != "全部")
                    sql += " AND REPAIR_ORDER_STATUS=" + Status;

                return Json(new { SearchSql = sql, Success = true, ErrorMsg = "" });
            }
            catch (Exception ex)
            {
                return Json(new { SearchSql = "", Success = false, ErrorMsg = ex.Message });
            }
        }
        #endregion

        #region 厂商维修周期明细查询
        public ActionResult RepairTimeDetail()
        {
            return View();
        }
        [GridAction]
        public ActionResult ListRepairTimeDetail(string sql, string searchSql, string Check_Day)
        {
            var result = getRepairDetail(sql, searchSql, Check_Day);
            var temp = new GridModel(result);
            return View(temp);
        }
        public IEnumerable<RepairTimeDetailModel> getRepairDetail(string sql, string searchSql, string Check_Day)
        {
            string sqlContent = " SELECT D.AGENT_NAME,B.OBU_NO,C.SHORTNAME,E.DICT_TITLE AS CURRENT_STATUS,B.CREATED_DATE,"
            + " DATEDIFF ( day  ,B.CREATED_DATE, A.CREATED_DATE  )+1-" + Check_Day + " REPAIR_DAYS "
            + " FROM ( SELECT DISTINCT REPAIR_ORDER_ID,MAX(CREATED_DATE) CREATED_DATE FROM REPAIR_ORDER_TRACKING " + sql
            + " GROUP BY REPAIR_ORDER_ID) AS A "
            + " INNER JOIN REPAIR_ORDERS AS B ON A.REPAIR_ORDER_ID=B.ID "
            + " INNER JOIN SUPPLIERS AS C ON B.SUPPLIER_ID=C.ID "
            + " INNER JOIN (SELECT AGENT_NO,AGENT_NAME,AGENT_TYPE_NO FROM ISSUE_RPT.DBO.ISSUER_AGENTS  "
            + " UNION ALL SELECT ISSUER_NO,ISSUER_NAME,'' AGENT_TYPE_NO FROM ISSUE_RPT.DBO.ISSUERS) AS D ON B.AGENT_NO=D.AGENT_NO "
            + " INNER JOIN (SELECT B.* FROM DICT_TYPE AS A INNER JOIN DICT_TYPE_VALUE AS B ON B.DICT_TYPE_ID=A.ID "
            + " WHERE A.TYPE_CODE='" + ConfigMgr.tblRepairOrderStatus + "') AS E ON B.STATUS=E.DICT_CODE "
            + " WHERE B.STATUS!=-1 " + searchSql;
            return db.Database.SqlQuery<RepairTimeDetailModel>(sqlContent);
        }
        [HttpPost]
        public ActionResult RepairTimeDetailSearch(DateTime? FromCreatedDate, DateTime? ToCreatedDate, string Status,
            string Supplier, string Agent, string txt_day, string txt_obuNo) //也可以用这种格式接收参数public ActionResult Search(FormCollection form)
        {
            try
            {
                string sql = "";
                string searchSql = "";
                if (FromCreatedDate != null && ToCreatedDate != null)
                    sql = " CREATED_DATE >='" + FromCreatedDate.Value + "' AND CREATED_DATE<'" + ToCreatedDate.Value.AddDays(1) + "'";
                if (Status != "全部")
                    sql += " AND REPAIR_ORDER_STATUS=" + Status;
                if (Supplier != "全部")
                    searchSql += " AND B.SUPPLIER_ID='" + Supplier + "'";
                if (Agent != "全部")
                    searchSql += " AND B.AGENT_NO='" + Agent + "'";
                if (txt_obuNo.Trim() != "")
                    searchSql += " AND B.OBU_NO ='" + txt_obuNo + "'";

                sql = sql == "" ? "" : " WHERE " + sql;
                return Json(new { Sql = sql, SearchSql = searchSql, Check_Day = txt_day.ChangAllTextToNull().Trim(), Success = true, ErrorMsg = "" });
            }
            catch (Exception ex)
            {
                return Json(new { Sql = "", SearchSql = "", Check_Day = 0, Success = false, ErrorMsg = ex.Message });
            }
        }
        #endregion

        #region 元器件使用情况日汇总
        public ActionResult PartsSaleCol()
        {
            return View();
        }
        [GridAction]
        public ActionResult ListPartSaleCol(string searchSql)
        {
            var result = getPartSaleInfo(searchSql, false);
            var temp = new GridModel(result);
            return View(temp);
        }
        public IEnumerable<PartSaleCollect> getPartSaleInfo(string searchSql, bool isGetSum)
        {
            try
            {
                searchSql = searchSql == "" ? "" : " WHERE " + searchSql;
                string sqlContent = " SELECT " + (isGetSum ? "" : "D.AGENT_NAME,C.SHORTNAME,B.SPEC_NAME,")
                    + " COUNT(CASE WHEN A.PARTS_ID=16 AND AMOUNT>0 THEN 1 END) 'BJ_SALE_COUNT',"
                    + " ISNULL(SUM(CASE WHEN A.PARTS_ID=16 AND AMOUNT>0 THEN AMOUNT END),0) 'BJ_SALE', "
                    + " COUNT(CASE WHEN A.PARTS_ID=17 AND AMOUNT>0 THEN 1 END) 'DZ_SALE_COUNT',"
                    + " ISNULL(SUM(CASE WHEN A.PARTS_ID=17 AND AMOUNT>0 THEN AMOUNT END),0) 'DZ_SALE',"
                    + " COUNT(CASE WHEN A.PARTS_ID=16 AND AMOUNT=0 THEN 1 END) 'BJ_FREE',"
                    + " COUNT(CASE WHEN A.PARTS_ID=17 AND AMOUNT=0 THEN 1 END) 'DZ_FREE' "
                    + " FROM PARTS_SALE AS A "
                    + " INNER JOIN PARTS_SPECS AS B ON A.PARTS_SPEC_ID=B.ID "
                    + " INNER JOIN SUPPLIERS AS C ON B.SUPPLIER_ID=C.ID "
                    + " INNER JOIN ISSUE_RPT.DBO.ISSUER_AGENTS AS D ON A.AGENT_NO=D.AGENT_NO " + searchSql + (searchSql.ToUpper().Contains("WHERE") ? " AND A.STATUS!=-1 " : " WHERE A.STATUS!=-1 ")
                    + (isGetSum ? "" : " GROUP BY D.AGENT_NAME,C.SHORTNAME,B.SPEC_NAME ");
                return db.Database.SqlQuery<PartSaleCollect>(sqlContent);
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }

        public ActionResult PartSaleSearch(DateTime? FromCreatedDate, DateTime? ToCreatedDate, string Agent, string Supplier)
        {
            try
            {

                List<string> querys = new List<string>();
                if (FromCreatedDate.HasValue)
                {
                    querys.Add(string.Format(" [REPORT_DATE]>='{0}' ", FromCreatedDate.Value.ToString("yyyy-MM-dd")));
                }

                if (ToCreatedDate.HasValue)
                {
                    querys.Add(string.Format(" [REPORT_DATE]<'{0}' ", ToCreatedDate.Value.AddDays(1).ToString("yyyy-MM-dd")));
                }

                if (Agent != "全部")
                {
                    querys.Add(string.Format(" A.[AGENT_NO]='{0}' ", Agent));
                }

                if (Supplier != "全部")
                {
                    querys.Add(string.Format(" A.SUPPLIER_ID='{0}' ", Supplier));
                }

                string searchSql = querys.ConcatToString(" and ");
                List<PartSaleCollect> result = getPartSaleInfo(searchSql, true).ToList();

                int bjSaleCount = 0, dzSaleCount = 0;
                decimal bjSale = 0, dzSale = 0, bjFree = 0, dzFree = 0;
                bjSaleCount += result[0].BJ_SALE_COUNT;
                dzSaleCount += result[0].DZ_SALE_COUNT;
                bjSale += result[0].BJ_SALE;
                dzSale += result[0].DZ_SALE;
                bjFree += result[0].BJ_FREE;
                dzFree += result[0].DZ_FREE;
                return Json(new { SearchSql = searchSql, Success = true, ErrorMsg = "", BJ_SALE_COUNT = bjSaleCount, DZ_SALE_COUNT = dzSaleCount, BJ_SALE = bjSale, DZ_SALE = dzSale, BJ_FREE = bjFree, DZ_FREE = dzFree });
            }
            catch (Exception ex)
            {

                return Json(new { SearchSql = "", Success = false, ErrorMsg = ex.Message });
            }
        }
        #endregion

        #region 元器件库存统计

        public ActionResult PartsStockCol()
        {
            return View();
        }
        public ActionResult ListPartsStock(DateTime? sDate, string AgentType, string Agent)
        {
            if (sDate == null)
                return Content("<Script>alert('请输入完整日期');window.location.href='/Report/PartsStockCol/';</Script>");
            List<string> querys = new List<string>();
            if (sDate.HasValue)
            {
                querys.Add(string.Format(" [SUM_DATE]='{0}' ", sDate.Value.ToString("yyyy-MM-dd")));
            }

            if (AgentType != "全部")
            {
                querys.Add(string.Format(" B.[AGENT_TYPE_NO]='{0}' ", AgentType));
            }

            if (Agent != "全部")
            {
                querys.Add(string.Format(" A.[AGENT_NO]='{0}' ", Agent));
            }

            string searchSql = querys.ConcatToString(" and ");

            string exportFileTitle = "元器件库存统计.xls";
            string template = Server.MapPath("/Templates/元器件库存统计.xls");
            ExportHelper export = new ExportHelper(template);

            searchSql = searchSql == "" ? "" : " AND " + searchSql;

            DataTable dtBJ = getStockCollect(searchSql, 16);
            DataTable dtDZ = getStockCollect(searchSql, 17);
            string agentName = "";
            if (dtBJ.Rows.Count <= 0 || dtDZ.Rows.Count <= 0)
            {
                agentName = db.Database.SqlQuery<string>("SELECT AGENT_NAME FROM ISSUE_RPT.DBO.ISSUER_AGENTS WHERE AGENT_NO='" + Agent + "'  UNION ALL SELECT ISSUER_NAME FROM ISSUE_RPT.DBO.ISSUERS  WHERE ISSUER_NO='" + Agent + "';").FirstOrDefault();
            }

            if (dtBJ.Rows.Count <= 0)
            {
                DataRow dr = dtBJ.NewRow();
                dr[0] = agentName;
                for (int i = 1; i < dtBJ.Columns.Count; i++)
                {
                    dr[i] = 0;
                }
                dtBJ.Rows.Add(dr);
            }
            if (dtDZ.Rows.Count <= 0)
            {
                DataRow dr = dtDZ.NewRow();
                dr[0] = agentName;
                for (int i = 1; i < dtDZ.Columns.Count; i++)
                {
                    dr[i] = 0;
                }
                dtDZ.Rows.Add(dr);
            }
            exportStock(export, dtBJ, 0, sDate.Value.ToShortDateString());
            exportStock(export, dtDZ, 1, sDate.Value.ToShortDateString());

            return export.DownLoad(this.File, exportFileTitle);
        }
        public DataTable getStockCollect(string searchSql, int partID)
        {
            try
            {
                DataTable dt = new DataTable();
                var partsSpecs = (from i in db.PARTS_SPECS.ToList()
                                  where i.PARTS_ID == partID && i.STATUS == DictTypeStatus.Normal.ToInt().Value
                                  select i).ToList();
                if (partsSpecs != null && partsSpecs.Count() > 0)
                {
                    string supplierContent = "";
                    foreach (var item in partsSpecs)
                    {
                        supplierContent += " ISNULL( SUM(CASE WHEN PARTS_SPEC_ID=" + item.ID + " THEN CURRENT_STOCK END) ,0) '" + item.SPEC_NAME + "',";
                    }
                    supplierContent = supplierContent.Substring(0, supplierContent.Length - 1);
                    string sqlContent = "SELECT B.AGENT_NAME," + supplierContent
                        + " FROM SUM_PARTS_STOCK  AS A "
                        + " INNER JOIN (SELECT AGENT_NO,AGENT_NAME,AGENT_TYPE_NO FROM ISSUE_RPT.DBO.ISSUER_AGENTS "
                        + " UNION ALL SELECT ISSUER_NO,ISSUER_NAME,''AGENT_TYPE_NO FROM ISSUE_RPT.DBO.ISSUERS) "
                        + " AS B ON A.AGENT_NO=B.AGENT_NO  WHERE PARTS_ID=" + partID + searchSql
                        + "  GROUP BY B.AGENT_NAME ORDER BY  B.AGENT_NAME";
                    dt = new DatabaseHelper().ExecuteSqlDataSet(sqlContent).Tables[0];
                }
                return dt;
            }
            catch (Exception ex)
            {

                throw;
            }

        }

        /// <summary>
        /// 导出库存统计数
        /// </summary>
        /// <param name="export"></param>
        /// <param name="sql"></param>
        /// <param name="searchSql"></param>
        /// <param name="FromCreatedDate"></param>
        /// <param name="ToCreatedDate"></param>
        private void exportStock(ExportHelper export, DataTable dt, int sheetIndex, string sDate)
        {
            int startRowIndex = 3; //主要数据起始行
            int startColIndex = 0; //主要数据起始列

            int printDateIndex = sheetIndex == 0 ? 9 : 11;

            //将合计行往后移动数据总行数一1
            export.ExportAddionalInfomation((excel, ep) =>
            {
                excel.ActiveSheetIndex = sheetIndex;
                excel.ShiftRows(4, 5, dt.Rows.Count - 1);
            });

            if (dt.Rows.Count > 0)
            {
                //导出主要数据
                export.ExportToExcel(dt.Rows.Cast<DataRow>(), (item, excel, rowIndex) =>
                {
                    int i = 0;
                    for (; i < item.Table.Columns.Count; i++)
                    {
                        if (i == startColIndex) //第一列写配件名
                        {
                            excel[rowIndex, i] = item[i].ToText();
                        }
                        else //其它列写数字
                        {
                            excel.SetCellValue(rowIndex, i, item[i].ToDouble());
                        }
                    }

                    //写入最后一列的合计信息
                    Char c1 = ('A'.CharToAscii() + startColIndex + 1).AsciiToChar();
                    Char c2 = ('A'.CharToAscii() + i - 1).AsciiToChar();
                    string formula = string.Format("SUM({0}{1}:{2}{1})", c1, rowIndex + 1, c2);
                    excel.SetCellFormula(rowIndex, i, formula);


                    //设置样式
                    for (int col = startColIndex; col <= i; col++)
                    {
                        excel.SetCellStyle(startRowIndex, startColIndex, rowIndex, col);
                    }


                }, sheetIndex, startRowIndex);

            }


            //导出合计，日期等其它的信息
            export.ExportAddionalInfomation((excel, ep) =>
            {
                excel.ActiveSheetIndex = sheetIndex;
                //合并标题的单元格式
                excel.Merge(0, 0, 0, dt.Columns.Count + 1);

                //写入统计日期
                excel[1, 1] = sDate;

                //设置表头
                int i = startColIndex + 1;
                for (; i < dt.Columns.Count; i++)
                {
                    excel[startRowIndex - 1, i] = dt.Columns[i].ColumnName;
                }

                if (dt != null && dt.Rows.Count > 0)
                {
                    //写表头的合计列列头
                    excel[startRowIndex - 1, i] = "合计";

                    //设置样式
                    for (int col = startColIndex + 1; col <= i; col++)
                    {
                        excel.SetCellStyle(startRowIndex, startColIndex + 1, startRowIndex - 1, col);
                    }

                    //写入日期

                    //写入最后一行的合计信息
                    excel[ep.LastRowIndex + 1, startColIndex] = "合计：";

                    for (int j = startColIndex + 1; j <= dt.Columns.Count; j++)
                    {
                        Char c = ('A'.CharToAscii() + j).AsciiToChar();

                        string formula = string.Format("SUM({0}{1}:{0}{2})", c, startRowIndex + 1, ep.LastRowIndex + 1);
                        excel.SetCellFormula(ep.LastRowIndex + 1, j, formula);
                    }
                }

                //设置样式
                for (int col = startColIndex; col <= dt.Columns.Count; col++)
                {
                    excel.SetCellStyle(startRowIndex, startColIndex, ep.LastRowIndex + 1, col);
                }

            });
        }

        #endregion

        #region 电子标签更换统计
        public ActionResult ObuChangeStatistics()
        {
            return View();
        }

        [GridAction]
        public ActionResult ListObuChange(string dateSql, string otherSql)
        {
            var result = GetObuChange(dateSql, otherSql);
            var temp = new GridModel(result);
            return View("RepairDaily", temp);
        }

        private List<ObuChangeStatisticsViewModel> GetObuChange(string dateSql, string otherSql)
        {
            try
            {
                string sql = "SELECT "
                    + " CASE WHEN  S.SHORTNAME IS NULL THEN '其它' ELSE S.SHORTNAME END '厂商' ,"
                    + "CASE WHEN D.SPEC_CODE IS NULL THEN '其它' ELSE D.SPEC_CODE END '型号',"
                    + "COUNT(CASE WHEN CHANGE_COUNT=1 THEN 1 END)'更换一次',"
                    + "COUNT(CASE WHEN CHANGE_COUNT=2 THEN 1 END)'更换两次',"
                    + "COUNT(CASE WHEN CHANGE_COUNT=3 THEN 1 END)'更换三次',"
                    + "COUNT(CASE WHEN CHANGE_COUNT>=4 THEN 1 END)'更换四次以上'FROM ("
                    + "SELECT LAST_OBU_NO,COUNT(1) CHANGE_COUNT "
                    + "FROM ISSUE_RPT.DBO.OBU_ISSUE "
                    + "WHERE LAST_OBU_NO IS NOT NULL AND LAST_OBU_NO!='' " + (string.IsNullOrEmpty(dateSql) ? "" : " AND " + dateSql)
                    + " GROUP BY LAST_OBU_NO ) AS A "
                    + "LEFT JOIN CONTRACT_DETAIL AS B ON A.LAST_OBU_NO BETWEEN B.START_NO AND B.END_NO AND B.STATUS!=-1 "
                    + "LEFT JOIN CONTRACTS AS CON ON B.CONTRACT_ID=CON.ID "
                    + "LEFT JOIN SUPPLIERS AS S ON CON.SECOND_PARTY=S.ID "
                    + "LEFT JOIN SPECS AS C ON B.VERSION_ID=C.ID "
                    + "LEFT JOIN DEVICE_VERSION AS D ON C.VERSION_ID=D.ID "
                    + otherSql
                    + " GROUP BY SHORTNAME,SPEC_CODE ORDER BY SHORTNAME,SPEC_CODE";
                return db.Database.SqlQuery<ObuChangeStatisticsViewModel>(sql).ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        [HttpPost]
        public ActionResult ObuChangeSearch(DateTime? sDate, DateTime? eDate, string Supplier, string flag)
        {
            try
            {
                string dateSql = "", otherSql = "", dateStr = "全部时间";
                if (sDate != null && eDate != null)
                {
                    dateSql = " REGISTE_DATE>='" + sDate.Value + "' AND REGISTE_DATE<'" + eDate.Value.AddDays(1) + "' ";
                    dateStr = sDate.Value.ToShortDateString() + "至" + eDate.Value.ToShortDateString();
                }
                if (Supplier != "全部")
                    otherSql += " WHERE S.ID=" + Supplier;
                if (flag == "report")
                    return ExcelObuChange(dateStr, dateSql, otherSql);
                else
                    return Content(new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(new { DateSql = dateSql, OtherSql = otherSql, Success = true, ErrorMsg = "" }));
            }
            catch (Exception ex)
            {
                if (flag == "report")
                    return Content("<script>alert('导出失败！');</script>");
                return Json(new { DateSql = "", OtherSql = "", Success = false, ErrorMsg = ex.Message });
            }
        }

        private ActionResult ExcelObuChange(string dateStr, string dateSql, string otherSql)
        {
            List<ObuChangeStatisticsViewModel> list = GetObuChange(dateSql, otherSql);
            if (list.Count > 0)
            {
                string exportFileTitle = "电子标签更换次数统计.xls";
                string template = Server.MapPath("/Templates/电子标签更换次数统计.xls");
                ExportHelper export = new ExportHelper(template);
                ExportObuChange(export, list, dateStr);
                return export.DownLoad(this.File, exportFileTitle);
            }
            else
            {
                return Content("<script>alert('该条件范围内数据不存在！');window.location.href='/Report/ObuChangeStatistics/';</script>");
            }
        }

        private void ExportObuChange(ExportHelper export, List<ObuChangeStatisticsViewModel> list, string sDate)
        {
            int startRowIndex = 3;//主要数据起始行
            //int startCellIndex = 0;//主要数据起始列
            int sheetIndex = 0;

            if (list.Count > 0)
            {
                export.ExportToExcel(list, (item, excel, rowIndex) =>
                {
                    excel[rowIndex, 0] = item.厂商;
                    excel[rowIndex, 1] = item.型号;
                    excel.SetCellValue(rowIndex, 2, item.更换一次);
                    excel.SetCellValue(rowIndex, 3, item.更换两次);
                    excel.SetCellValue(rowIndex, 4, item.更换三次);
                    excel.SetCellValue(rowIndex, 5, item.更换四次以上);
                    for (int col = startRowIndex + 1; col < list.Count + startRowIndex; col++)
                    {
                        excel.SetCellStyle(startRowIndex, 0, col, 0);
                        excel.SetCellStyle(startRowIndex, 1, col, 1);
                        excel.SetCellStyle(startRowIndex, 2, col, 2);
                        excel.SetCellStyle(startRowIndex, 3, col, 3);
                        excel.SetCellStyle(startRowIndex, 4, col, 4);
                        excel.SetCellStyle(startRowIndex, 5, col, 5);
                    }
                }, sheetIndex, startRowIndex);

                export.ExportAddionalInfomation((excel, ep) =>
                {
                    //制表人
                    var loginer = UIUtility.GetCurrentUser();
                    excel[1, 3] = "日期：" + sDate;
                    excel[ep.LastRowIndex + 1, 0] = "合计：";

                    for (int j = 2; j <= 5; j++)
                    {
                        Char c = ('A'.CharToAscii() + j).AsciiToChar();

                        string formula = string.Format("SUM({0}{1}:{0}{2})", c, startRowIndex + 1, ep.LastRowIndex + 1);
                        excel.SetCellFormula(ep.LastRowIndex + 1, j, formula);
                    }
                    for (int col = 0; col <= 5; col++)
                    {
                        excel.SetCellStyle(startRowIndex, col, ep.LastRowIndex + 1, col);
                    }
                });
            }
        }

        #endregion
    }
}
